using Dapper;
using Lzfy_Refund_Service.Models;
using Lzfy_Refund_Service.Services;
using System.Text;

namespace Lzfy_Refund_Service.Repositories
{
    public class RoleRepository : IRoleRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;

        public RoleRepository(IDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<RoleListResponse> GetRoleListAsync(RoleListRequest request)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();
            
            if (!string.IsNullOrEmpty(request.RoleName))
            {
                whereConditions.Add("role_name LIKE @RoleName");
                parameters.Add("@RoleName", $"%{request.RoleName}%");
            }
            
            if (!string.IsNullOrEmpty(request.RoleCode))
            {
                whereConditions.Add("role_code LIKE @RoleCode");
                parameters.Add("@RoleCode", $"%{request.RoleCode}%");
            }
            
            if (request.IsActive.HasValue)
            {
                whereConditions.Add("is_active = @IsActive");
                parameters.Add("@IsActive", request.IsActive.Value);
            }
            
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            
            // 获取总数
            var countSql = $"SELECT COUNT(*) FROM sys_roles {whereClause}";
            var total = await connection.QuerySingleAsync<int>(countSql, parameters);
            
            // 获取分页数据
            var offset = (request.Page - 1) * request.PageSize;
            var dataSql = $@"
                SELECT id as Id, input_date as InputDate, role_name as RoleName, 
                       role_code as RoleCode, description as Description, 
                       is_active as IsActive, sort_order as SortOrder
                FROM sys_roles 
                {whereClause}
                ORDER BY sort_order, id
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            
            parameters.Add("@Offset", offset);
            parameters.Add("@PageSize", request.PageSize);
            
            var roles = await connection.QueryAsync<RoleDto>(dataSql, parameters);
            
            return new RoleListResponse
            {
                Roles = roles.ToList(),
                Total = total,
                Page = request.Page,
                PageSize = request.PageSize
            };
        }

        public async Task<Role?> GetRoleByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT id as Id, input_date as InputDate, role_name as RoleName, 
                       role_code as RoleCode, description as Description, 
                       is_active as IsActive, sort_order as SortOrder
                FROM sys_roles 
                WHERE id = @Id";
            
            return await connection.QuerySingleOrDefaultAsync<Role>(sql, new { Id = id });
        }

        public async Task<Role?> GetRoleByCodeAsync(string roleCode)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT id as Id, input_date as InputDate, role_name as RoleName, 
                       role_code as RoleCode, description as Description, 
                       is_active as IsActive, sort_order as SortOrder
                FROM sys_roles 
                WHERE role_code = @RoleCode";
            
            return await connection.QuerySingleOrDefaultAsync<Role>(sql, new { RoleCode = roleCode });
        }

        public async Task<int> AddRoleAsync(Role role)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                INSERT INTO sys_roles (role_name, role_code, description, is_active, sort_order)
                VALUES (@RoleName, @RoleCode, @Description, @IsActive, @SortOrder);
                SELECT CAST(SCOPE_IDENTITY() as int);";
            
            return await connection.QuerySingleAsync<int>(sql, role);
        }

        public async Task<bool> UpdateRoleAsync(Role role)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                UPDATE sys_roles 
                SET role_name = @RoleName, role_code = @RoleCode, 
                    description = @Description, is_active = @IsActive, 
                    sort_order = @SortOrder
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, role);
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteRoleAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            // 检查是否有用户关联此角色
            var checkSql = "SELECT COUNT(*) FROM sys_user_roles WHERE role_id = @Id";
            var userCount = await connection.QuerySingleAsync<int>(checkSql, new { Id = id });
            
            if (userCount > 0)
            {
                throw new InvalidOperationException("该角色下还有用户，无法删除");
            }
            
            var sql = "DELETE FROM sys_roles WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }

        public async Task<bool> ToggleRoleStatusAsync(int id, bool isActive)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "UPDATE sys_roles SET is_active = @IsActive WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id, IsActive = isActive });
            return rowsAffected > 0;
        }

        public async Task<List<RoleDto>> GetAllActiveRolesAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT id as Id, input_date as InputDate, role_name as RoleName, 
                       role_code as RoleCode, description as Description, 
                       is_active as IsActive, sort_order as SortOrder
                FROM sys_roles 
                WHERE is_active = 1
                ORDER BY sort_order, id";
            
            var roles = await connection.QueryAsync<RoleDto>(sql);
            return roles.ToList();
        }

        public async Task<bool> RoleCodeExistsAsync(string roleCode, int? excludeId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "SELECT COUNT(*) FROM sys_roles WHERE role_code = @RoleCode";
            var parameters = new DynamicParameters();
            parameters.Add("@RoleCode", roleCode);
            
            if (excludeId.HasValue)
            {
                sql += " AND id != @ExcludeId";
                parameters.Add("@ExcludeId", excludeId.Value);
            }
            
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }
    }
}